﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using FX.CC.Andon.Entity.ReportManage;
using FX.CC.Andon.Utility.Common;
using FX.CC.Andon.Utility.DataBase;

namespace FX.CC.Andon.Service.ReportManage
{
    public class ModelRecService : DbHelperBase
    {
        public int GetModelRecordReportCount(ModelRecQcEntity qcmodel)
        {
            var count = 0;
            var strSql = new StringBuilder();
            strSql.Append(" select  count(*) AS [count] FROM UT_ModelRec AS A ");
            strSql.Append(" LEFT JOIN UT_Flow AS B ON A.FlowID=B.FlowID  ");
            strSql.Append(" LEFT JOIN UT_Product AS C ON B.PrdID=C.PrdID  ");
            strSql.Append(" WHERE A.EndTime IS NOT NULL ");
            if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
            {
                strSql.Append(" and A.BizDate>=@BizDateFrom ");
            }
            if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
            {
                strSql.Append(" and A.BizDate<=@BizDateEnd");
            }
            if (!string.IsNullOrEmpty(qcmodel.PrdID))
            {
                strSql.Append(" and C.PrdID =@PrdID ");
            }
            if (!string.IsNullOrEmpty(qcmodel.FlowID))
            {
                strSql.Append(" and B.FlowID =@FlowID ");
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
                    {
                        var dateFrom = Convert.ToDateTime(qcmodel.BizDateFrom);
                        Db.AddInParameter(cmd, "@BizDateFrom", dateFrom);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
                    {
                        var dateEnd = Convert.ToDateTime(qcmodel.BizDateEnd);
                        Db.AddInParameter(cmd, "@BizDateEnd", dateEnd);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.PrdID))
                    {
                        Db.AddInParameter(cmd, "@PrdID", qcmodel.PrdID);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.FlowID))
                    {
                        Db.AddInParameter(cmd, "@FlowID", qcmodel.FlowID);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["count"]);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<ModelRecEntity> GetModelRecordReportList(ModelRecQcEntity qcmodel)
        {
            var list = new List<ModelRecEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            var start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            var end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by A.BizDate DESC ) AS idx,A.RecID,B.ProcessIdx,C.PrdCode,C.PrdName,A.BizDate,A.StartTime,A.EndTime,datediff(second,A.StartTime,A.EndTime) as ElapsedTime,A.ModelMin FROM UT_ModelRec AS A   ");
            strSql.Append(" LEFT JOIN UT_Flow AS B ON A.FlowID=B.FlowID  ");
            strSql.Append(" LEFT JOIN UT_Product AS C ON B.PrdID=C.PrdID  ");
            strSql.Append(" WHERE A.EndTime IS NOT NULL ");
            if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
            {
                strSql.Append(" and A.BizDate>=@BizDateFrom ");
            }
            if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
            {
                strSql.Append(" and A.BizDate<=@BizDateEnd");
            }
            if (!string.IsNullOrEmpty(qcmodel.PrdID))
            {
                strSql.Append(" and C.PrdID =@PrdID ");
            }
            if (!string.IsNullOrEmpty(qcmodel.FlowID))
            {
                strSql.Append(" and B.FlowID =@FlowID ");
            }
            strSql.Append(") AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
                    {
                        var dateFrom = Convert.ToDateTime(qcmodel.BizDateFrom);
                        Db.AddInParameter(cmd, "@BizDateFrom", dateFrom);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
                    {
                        var dateEnd = Convert.ToDateTime(qcmodel.BizDateEnd);
                        Db.AddInParameter(cmd, "@BizDateEnd", dateEnd);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.PrdID))
                    {
                        Db.AddInParameter(cmd, "@PrdID", qcmodel.PrdID);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.FlowID))
                    {
                        Db.AddInParameter(cmd, "@FlowID", qcmodel.FlowID);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {

                            var model = new ModelRecEntity();
                            if (reader["RecID"] != DBNull.Value)
                                model.RecID = ReaderHelper.GetValue<Guid>(reader, "RecID");
                            if (reader["ProcessIdx"] != DBNull.Value)
                                model.ProcessIdx = Convert.ToInt32(reader["ProcessIdx"]);
                            model.PrdCode = reader["PrdCode"] == obj ? null : Convert.ToString(reader["PrdCode"]);
                            model.PrdName = reader["PrdName"] == obj ? null : Convert.ToString(reader["PrdName"]);
                            if (reader["BizDate"] != DBNull.Value)
                                model.BizDate = Convert.ToDateTime(reader["BizDate"]);
                            if (reader["StartTime"] != DBNull.Value)
                                model.StartTime = Convert.ToDateTime(reader["StartTime"]);
                            if (reader["EndTime"] != DBNull.Value)
                                model.EndTime = Convert.ToDateTime(reader["EndTime"]);
                            if (reader["ElapsedTime"] != DBNull.Value)
                                model.ElapsedTime = Math.Round(Convert.ToDecimal(reader["ElapsedTime"]) / 60, 2);
                            if (reader["ModelMin"] != DBNull.Value)
                                model.ModelMin = Convert.ToDecimal(reader["ModelMin"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public int GetModelAvgReportCount(ModelRecQcEntity qcmodel)
        {
            var count = 0;
            var strSql = new StringBuilder();
            strSql.Append(" select  count(*) AS [count] FROM ( SELECT A.FlowID,AVG(datediff(second,A.StartTime,A.EndTime)) as AvgTime FROM UT_ModelRec AS A WHERE A.EndTime IS NOT NULL ");
            if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
            {
                strSql.Append(" and A.BizDate>=@BizDateFrom ");
            }
            if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
            {
                strSql.Append(" and A.BizDate<=@BizDateEnd");
            }

            strSql.Append(" GROUP BY A.FlowID )AS T ");
            strSql.Append(" INNER JOIN UT_Flow AS B ON T.FlowID=B.FlowID INNER JOIN UT_Product AS C ON B.PrdID=C.PrdID where 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.PrdID))
            {
                strSql.Append(" and C.PrdID =@PrdID ");
            }
            if (!string.IsNullOrEmpty(qcmodel.FlowID))
            {
                strSql.Append(" and B.FlowID =@FlowID ");
            }

            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
                    {
                        var dateFrom = Convert.ToDateTime(qcmodel.BizDateFrom);
                        Db.AddInParameter(cmd, "@BizDateFrom", dateFrom);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
                    {
                        var dateEnd = Convert.ToDateTime(qcmodel.BizDateEnd);
                        Db.AddInParameter(cmd, "@BizDateEnd", dateEnd);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.PrdID))
                    {
                        Db.AddInParameter(cmd, "@PrdID", qcmodel.PrdID);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.FlowID))
                    {
                        Db.AddInParameter(cmd, "@FlowID", qcmodel.FlowID);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["count"]);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<ModelRecEntity> GetModelAvgReportList(ModelRecQcEntity qcmodel)
        {
            var list = new List<ModelRecEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            var start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            var end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT X.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by C.PrdCode,B.ProcessIdx ) AS idx,B.FlowID,B.ProcessIdx,C.PrdCode,C.PrdName,T.AvgTime,B.ModelMin FROM (  ");
            strSql.Append(" SELECT A.FlowID,AVG(datediff(second,A.StartTime,A.EndTime)) as AvgTime FROM UT_ModelRec AS A WHERE A.EndTime IS NOT NULL ");
            if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
            {
                strSql.Append(" and A.BizDate>=@BizDateFrom ");
            }
            if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
            {
                strSql.Append(" and A.BizDate<=@BizDateEnd");
            }
            strSql.Append(" GROUP BY A.FlowID )AS T ");
            strSql.Append(" INNER JOIN UT_Flow AS B ON T.FlowID=B.FlowID INNER JOIN UT_Product AS C ON B.PrdID=C.PrdID where 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.FlowID))
            {
                strSql.Append(" and B.FlowID =@FlowID ");
            }
            if (!string.IsNullOrEmpty(qcmodel.PrdID))
            {
                strSql.Append(" and B.PrdID =@PrdID ");
            }
            strSql.Append(") AS X ");
            strSql.Append(" Where X.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(qcmodel.BizDateFrom))
                    {
                        var dateFrom = Convert.ToDateTime(qcmodel.BizDateFrom);
                        Db.AddInParameter(cmd, "@BizDateFrom", dateFrom);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.BizDateEnd))
                    {
                        var dateEnd = Convert.ToDateTime(qcmodel.BizDateEnd);
                        Db.AddInParameter(cmd, "@BizDateEnd", dateEnd);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.FlowID))
                    {
                        Db.AddInParameter(cmd, "@FlowID", qcmodel.FlowID);
                    }
                    if (!string.IsNullOrEmpty(qcmodel.PrdID))
                    {
                        Db.AddInParameter(cmd, "@PrdID", qcmodel.PrdID);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {

                            var model = new ModelRecEntity();

                            if (reader["FlowID"] != DBNull.Value)
                                model.FlowID = new Guid(reader["FlowID"].ToString());
                            if (reader["ProcessIdx"] != DBNull.Value)
                                model.ProcessIdx = Convert.ToInt32(reader["ProcessIdx"]);
                            model.PrdCode = reader["PrdCode"] == obj ? null : Convert.ToString(reader["PrdCode"]);
                            model.PrdName = reader["PrdName"] == obj ? null : Convert.ToString(reader["PrdName"]);
                            if (reader["AvgTime"] != DBNull.Value)
                                model.ElapsedTime = Math.Round(Convert.ToDecimal(reader["AvgTime"]) / 60, 2);
                            if (reader["ModelMin"] != DBNull.Value)
                                model.ModelMin = Convert.ToDecimal(reader["ModelMin"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }
    }
}
